﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public class JChuJiaGongWuListDAL
    {
        /// <summary>
        /// 新增初加工管制票
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int Add(JChuJiaGongWuListInfo obj,List<JChuJiaItemDetailInfo> itemList,List<JChuJiaSerialDetailInfo> serialList,List<JChuJiaFlowDetailInfo> flowList)
        {
            int ChuJGWId = 0;
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_Add");
                    DB.AddInParameter(dbCommand, "MacType", DbType.String, obj.MacType);
                    DB.AddInParameter(dbCommand, "SupplerName", DbType.String, obj.SupplerName);
                    DB.AddInParameter(dbCommand, "ControlReasonType", DbType.String, obj.ControlReasonType);
                    DB.AddInParameter(dbCommand, "ControlReasonSoure", DbType.String, obj.ControlReasonSoure);
                    DB.AddInParameter(dbCommand, "AddUserId", DbType.Int32, obj.AddUserId);
                    DB.AddInParameter(dbCommand, "AddRealName", DbType.String, obj.AddRealName);
                    //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                    ChuJGWId= Convert.ToInt32(DB.ExecuteScalar(dbCommand,trans));
                    //添加管制内容
                    foreach (JChuJiaItemDetailInfo detialItem in itemList)
                    {
                        DbCommand dbCommanddetialItem = DB.GetStoredProcCommand("JChuJiaGongWuList_AddJChuJiaItemDetail");
                        DB.AddInParameter(dbCommanddetialItem, "ChuJGWId", DbType.Int32, ChuJGWId);
                        DB.AddInParameter(dbCommanddetialItem, "ItemCode", DbType.String, detialItem.ItemCode);
                        DB.AddInParameter(dbCommanddetialItem, "ItemName", DbType.String, detialItem.ItemName);
                        DB.AddInParameter(dbCommanddetialItem, "Quantity", DbType.Int32, detialItem.Quantity);
                        DB.AddInParameter(dbCommanddetialItem, "Note", DbType.String, detialItem.Note);

                        //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                        int detialItemID=Convert.ToInt32(DB.ExecuteScalar(dbCommanddetialItem, trans));
                    }
                    //添加管制机号
                    foreach (JChuJiaSerialDetailInfo serialItem in serialList)
                    {
                        DbCommand dbCommandserialItem = DB.GetStoredProcCommand("JChuJiaGongWuList_AddJChuJiaSerialDetail");
                        DB.AddInParameter(dbCommandserialItem, "ChuJGWId", DbType.Int32, ChuJGWId);
                        DB.AddInParameter(dbCommandserialItem, "JHSerialNumber", DbType.String, serialItem.JHSerialNumber);
                        DB.AddInParameter(dbCommandserialItem, "SJSerialNumber", DbType.String, serialItem.SJSerialNumber);
                        DB.AddInParameter(dbCommandserialItem, "Note", DbType.String, serialItem.Note);

                        //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                        int serialItemID = Convert.ToInt32(DB.ExecuteScalar(dbCommandserialItem, trans));
                    }
                    int FlowDetailID = 0;
                    int FistChuFlowId = 0;//第一个流程ID
                    //添加流程信息
                    foreach (JChuJiaFlowDetailInfo flowItem in flowList)
                    {
                        DbCommand dbCommandFlowDetail = DB.GetStoredProcCommand("JChuJiaGongWuList_AddJChuJiaFlowDetail");
                        DB.AddInParameter(dbCommandFlowDetail, "ChuJGWId", DbType.Int32, ChuJGWId);
                        DB.AddInParameter(dbCommandFlowDetail, "ChuFlowTempId", DbType.Int32, flowItem.ChuFlowTempId);
                        DB.AddInParameter(dbCommandFlowDetail, "PreChuFlowID", DbType.Int32, FlowDetailID);
                        DB.AddInParameter(dbCommandFlowDetail, "DeptTitle", DbType.String, flowItem.DeptTitle);
                        DB.AddInParameter(dbCommandFlowDetail, "UserId", DbType.Int32, flowItem.UserId);
                        DB.AddInParameter(dbCommandFlowDetail, "RealName", DbType.String, flowItem.RealName);
                        DB.AddInParameter(dbCommandFlowDetail, "CBDate", DbType.String, flowItem.CBDate);
                        DB.AddInParameter(dbCommandFlowDetail, "CBNote", DbType.String, flowItem.CBNote);
                        DB.AddInParameter(dbCommandFlowDetail, "CBResult", DbType.String, flowItem.CBResult);
                        DB.AddInParameter(dbCommandFlowDetail, "IsSend", DbType.Boolean, flowItem.IsSend);

                        //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                        FlowDetailID = Convert.ToInt32(DB.ExecuteScalar(dbCommandFlowDetail, trans));
                        FistChuFlowId = FistChuFlowId > 0 ? FistChuFlowId : FlowDetailID;//
                    }
                    ///更新下个承办人信息，如果完成，则写完成
                    DbCommand dbCommandUpdateCurrFlow = DB.GetStoredProcCommand("JChuJiaGongWuList_UpdateCurrFlow", FistChuFlowId);
                    DB.ExecuteScalar(dbCommandUpdateCurrFlow, trans);

                    trans.Commit();
                    return ChuJGWId;
                }
                catch(Exception exc)
                {
                    return -1;
                    trans.Rollback();                
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }

           
        }

        public static bool Update(JChuJiaGongWuListInfo obj, List<JChuJiaItemDetailInfo> itemList, List<JChuJiaSerialDetailInfo> serialList)
        {            
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {

                    DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_Update");
                    DB.AddInParameter(dbCommand, "ChuJGWId", DbType.Int32, obj.ChuJGWId);
                    DB.AddInParameter(dbCommand, "MacType", DbType.String, obj.MacType);
                    DB.AddInParameter(dbCommand, "SupplerName", DbType.String, obj.SupplerName);
                    DB.AddInParameter(dbCommand, "ControlReasonType", DbType.String, obj.ControlReasonType);
                    DB.AddInParameter(dbCommand, "ControlReasonSoure", DbType.String, obj.ControlReasonSoure);
                    DB.AddInParameter(dbCommand, "ScanFilePath", DbType.String, obj.ScanFilePath);
                    DB.AddInParameter(dbCommand, "ScanDate", DbType.String, obj.ScanDate);
                    DB.ExecuteScalar(dbCommand, trans);

                    DbCommand DeleteJChuJiaItemDetail = DB.GetStoredProcCommand("JChuJiaGongWuList_DeleteJChuJiaItemDetailByChuJGWId", obj.ChuJGWId);
                    DB.ExecuteScalar(DeleteJChuJiaItemDetail,trans);
                    //添加管制内容
                    foreach (JChuJiaItemDetailInfo detialItem in itemList)
                    {
                        DbCommand dbCommanddetialItem = DB.GetStoredProcCommand("JChuJiaGongWuList_AddJChuJiaItemDetail");
                        DB.AddInParameter(dbCommanddetialItem, "ChuJGWId", DbType.Int32, obj.ChuJGWId);
                        DB.AddInParameter(dbCommanddetialItem, "ItemCode", DbType.String, detialItem.ItemCode);
                        DB.AddInParameter(dbCommanddetialItem, "ItemName", DbType.String, detialItem.ItemName);
                        DB.AddInParameter(dbCommanddetialItem, "Quantity", DbType.Int32, detialItem.Quantity);
                        DB.AddInParameter(dbCommanddetialItem, "Note", DbType.String, detialItem.Note);

                        //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                        int detialItemID = Convert.ToInt32(DB.ExecuteScalar(dbCommanddetialItem, trans));
                    }

                    DbCommand DeleteJChuJiaSerialDetail = DB.GetStoredProcCommand("JChuJiaGongWuList_DeleteJChuJiaSerialDetailByChuJGWId", obj.ChuJGWId);
                    DB.ExecuteScalar(DeleteJChuJiaSerialDetail, trans);

                    //添加管制机号
                    foreach (JChuJiaSerialDetailInfo serialItem in serialList)
                    {
                        DbCommand dbCommandserialItem = DB.GetStoredProcCommand("JChuJiaGongWuList_AddJChuJiaSerialDetail");
                        DB.AddInParameter(dbCommandserialItem, "ChuJGWId", DbType.Int32, obj.ChuJGWId);
                        DB.AddInParameter(dbCommandserialItem, "JHSerialNumber", DbType.String, serialItem.JHSerialNumber);
                        DB.AddInParameter(dbCommandserialItem, "SJSerialNumber", DbType.String, serialItem.SJSerialNumber);
                        DB.AddInParameter(dbCommandserialItem, "Note", DbType.String, serialItem.Note);

                        //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                        int serialItemID = Convert.ToInt32(DB.ExecuteScalar(dbCommandserialItem, trans));
                    }

                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }

        
        /// <summary>
        /// 删除初加工管理表
        /// </summary>
        /// <param name="ChuJGWId"></param>
        /// <returns></returns>
        public static bool Delete(int ChuJGWId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_Delete", ChuJGWId);
            try
            {
                DB.ExecuteScalar(dbCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }
       
        public static IDataReader GetAllJChuJiaFlowDetail(int ChuJGWId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetAllJChuJiaFlowDetail", ChuJGWId);
            return DB.ExecuteReader(dbCommand);
        }
        public static IDataReader GetAllJChuJiaItemDetailByChuJGWId(int ChuJGWId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetAllJChuJiaItemDetailByChuJGWId", ChuJGWId);
            return DB.ExecuteReader(dbCommand);
        }
        public static IDataReader GetAllJChuJiaSerialDetailByChuJGWId(int ChuJGWId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetAllJChuJiaSerialDetailByChuJGWId", ChuJGWId);
            return DB.ExecuteReader(dbCommand);
        }
        public static IDataReader GetAllJChuJiaTempFlow()
	    {
		    Database DB = DatabaseFactory.CreateDatabase();		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetAllJChuJiaTempFlow");
            return DB.ExecuteReader(dbCommand);
	    }

        public static IDataReader GetListByID(int ChuJGWId)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetListByID", ChuJGWId);
            return DB.ExecuteReader(dbCommand);
        }
        public static IDataReader GetListJChuJiaFlowDetail(int ChuFlowID)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetListJChuJiaFlowDetail", ChuFlowID);
            return DB.ExecuteReader(dbCommand);
        }
        public static DataTable GetPagination(string whereSql, int PageIndex, int PageSize, string Sort, ref int TotalCount)
        {
            Database DB = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_GetPagination");
            DB.AddInParameter(dbCommand, "whereSql", DbType.String, whereSql);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32, PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32, 8);
            DataTable dt = DB.ExecuteDataSet(dbCommand).Tables[0];

            TotalCount = Convert.ToInt32(DB.GetParameterValue(dbCommand, "TotalCount"));
            return dt;
        }
       
        public static bool UpdateJChuJiaFlowDetail(JChuJiaFlowDetailInfo obj,List<JChuJiaSerialDetailInfo> listSerial)
        {
            Database DB = DatabaseFactory.CreateDatabase();
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                DbTransaction trans = conn.BeginTransaction();
                try
                {                  

                    DbCommand dbCommand = DB.GetStoredProcCommand("JChuJiaGongWuList_UpdateJChuJiaFlowDetail");
                    DB.AddInParameter(dbCommand, "ChuFlowID", DbType.Int32, obj.ChuFlowID);
                    DB.AddInParameter(dbCommand, "CBDate", DbType.String, obj.CBDate);
                    DB.AddInParameter(dbCommand, "CBNote", DbType.String, obj.CBNote);
                    DB.AddInParameter(dbCommand, "CBResult", DbType.String, obj.CBResult);
                    DB.AddInParameter(dbCommand, "IsSend", DbType.Boolean, true);
                    DB.ExecuteScalar(dbCommand, trans);

                    DbCommand DeleteJChuJiaSerialDetail = DB.GetStoredProcCommand("JChuJiaGongWuList_DeleteJChuJiaSerialDetailByChuJGWId", obj.ChuJGWId);
                    DB.ExecuteScalar(DeleteJChuJiaSerialDetail, trans);

                    //添加管制机号
                    foreach (JChuJiaSerialDetailInfo serialItem in listSerial)
                    {
                        DbCommand dbCommandserialItem = DB.GetStoredProcCommand("JChuJiaGongWuList_AddJChuJiaSerialDetail");
                        DB.AddInParameter(dbCommandserialItem, "ChuJGWId", DbType.Int32, obj.ChuJGWId);
                        DB.AddInParameter(dbCommandserialItem, "JHSerialNumber", DbType.String, serialItem.JHSerialNumber);
                        DB.AddInParameter(dbCommandserialItem, "SJSerialNumber", DbType.String, serialItem.SJSerialNumber);
                        DB.AddInParameter(dbCommandserialItem, "Note", DbType.String, serialItem.Note);

                        //return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
                        int serialItemID = Convert.ToInt32(DB.ExecuteScalar(dbCommandserialItem, trans));
                    }
                    ///更新下个承办人信息，如果完成，则写完成
                    DbCommand dbCommandUpdateCurrFlow = DB.GetStoredProcCommand("JChuJiaGongWuList_UpdateCurrFlow", obj.ChuFlowID);
                    DB.ExecuteScalar(dbCommandUpdateCurrFlow, trans);
                   
                    trans.Commit();
                    return true;
                }
                catch (Exception exc)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }


        }
    }
}
